Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# CREATE OPERATOR

Purpose

Use the `CREATE` `OPERATOR` statement to create a new operator and define its bindings.

Operators can be referenced by indextypes and by SQL queries and DML statements. The operators, in turn, reference functions, packages, types, and other user-defined objects.

See Also: Oracle Data Cartridge Developer's Guide and Oracle Database Concepts for a discussion of these dependencies and of operators in general

Prerequisites

To create an operator in your own schema, you must have the `CREATE` `OPERATOR` system privilege. To create an operator in another schema, you must have the `CREATE` `ANY` `OPERATOR` system privilege. In either case, you must also have the `EXECUTE` object privilege on the functions and operators referenced.

Syntax

create_operator::=

Description of the illustration create_operator.gif

binding_clause::=

Description of the illustration binding_clause.gif

implementation_clause::=

Description of the illustration implementation_clause.gif

context_clause::=

Description of the illustration context_clause.gif

using_function_clause::=

Description of the illustration using_function_clause.gif

Semantics

OR REPLACE

Specify `OR` `REPLACE` to replace the definition of the operator schema object.

Restriction on Replacing an Operator You can replace the definition only if the operator has no dependent objects, such as indextypes supporting the operator.

schema

Specify the schema containing the operator. If you omit `schema`, then the database creates the operator in your own schema.

operator

Specify the name of the operator to be created.

binding_clause

Use the `binding_clause` to specify one or more parameter datatypes (`parameter_type`) for binding the operator to a function. The signature of each binding--that is, the sequence of the datatypes of the arguments to the corresponding function--must be unique according to the rules of overloading.

The `parameter_type` can itself be an object type. If it is, then you can optionally qualify it with its schema.

Restriction on Binding Operators You cannot specify a `parameter_type` of `REF`, `LONG`, or `LONG` `RAW`.

See Also: PL/SQL User's Guide and Reference for more information about overloading

RETURN Clause ``

Specify the return datatype for the binding.

The `return_type` can itself be an object type. If so, then you can optionally qualify it with its schema.

Restriction on Binding Return Datatype You cannot specify a `return_type` of `REF`, `LONG`, or `LONG` `RAW`.

implementation_clause

Use this clause to describe the implementation of the binding.

ANCILLARY TO Clause

Use the `ANCILLARY` `TO` clause to indicate that the operator binding is ancillary to the specified primary operator binding (`primary_operator`). If you specify this clause, then do not specify a previous binding with just one number parameter.

context_clause

Use the `context_clause` to describe the functional implementation of a binding that is not ancillary to a primary operator binding.

WITH INDEX CONTEXT, SCAN CONTEXT Use this clause to indicate that the functional evaluation of the operator uses the index and a scan context that is specified by the implementation type.

COMPUTE ANCILLARY DATA Specify `COMPUTE` `ANCILLARY` `DATA` to indicate that the operator binding computes ancillary data.

WITH COLUMN CONTEXT Specify `WITH` `COLUMN` `CONTEXT` to indicate that Oracle Database should pass the column information to the functional implementation for the operator.

If you specify this clause, then the signature of the function implemented must include one extra `ODCIFuncCallInfo` structure.

See Also: Oracle Data Cartridge Developer's Guide for instructions on using the `ODCIFuncCallInfo` routine

using_function_clause

The `using_function_clause` lets you specify the function that provides the implementation for the binding. The `function_name` can be a standalone function, packaged function, type method, or a synonym for any of these.

If the function is subsequently dropped, then the database marks all dependent objects `INVALID`, including the operator. However, if you then subsequently issue an `ALTER` `OPERATOR` ... `DROP` `BINDING` statement to drop the binding, then subsequent queries and DML will revalidate the dependent objects.

Example

Creating User-Defined Operators: Example This example creates a very simple functional implementation of equality and then creates an operator that uses the function:

    CREATE FUNCTION eq_f(a VARCHAR2, b VARCHAR2) RETURN NUMBER AS
    BEGIN
       IF a = b THEN RETURN 1;
       ELSE RETURN 0;
       END IF;
    END;
    /

    CREATE OPERATOR eq_op
       BINDING (VARCHAR2, VARCHAR2)
       RETURN NUMBER
       USING eq_f;